#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : wh_level_calendar.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-12-16 11:35 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils.log_util import log


def house_calendar():
    sql = """select concat(lh.id ,'') house_id,
            curdate() dt,
            if(lh.state = 100  ,1,0) online_stock ,
            if (lhs.state=0 or l.state <> 0 or l.state_super <> 10 or lh.state <> 100,0,1) rent_stock,
            sum(if(curdate() <> bo.actcheckout_day ,1,0)) have_order,
            round(sum(if(curdate() <> bo.actcheckout_day ,if(bo.cancel_status = 0,bo.real_income,bo.cut_part)/datediff(bo.actcheckout_day,bo.actcheckin_day),0))) income,
            sum(if(bo.actcheckin_day = bo.actcheckout_day ,if(bo.cancel_status = 0,bo.real_income,bo.cut_part),0)) compensate_income,
            sum(if(curdate() = bo.actcheckout_day and bo.actcheckout_day <> bo.actcheckin_day,1,0)) checkout_orders ,
            group_concat(distinct if(curdate() = bo.actcheckout_day and bo.actcheckout_day <> bo.actcheckin_day,bo.order_channel,null)) checkout_channel ,
            group_concat(distinct if(curdate() = bo.actcheckout_day and bo.actcheckout_day <> bo.actcheckin_day,bo.id,null)) checkout_order_id 
            from youjia.lod_house lh
            left join youjia.lodge l on l.id = lh.lodge_id
            left join youjia.lod_house_stock lhs on lhs.house_id = lh.id and lhs.dt = curdate()
            left join youjia.order_detail od on od.house_id = lh.id 
            left join youjia.orders bo on bo.id = od.order_id and bo.cancel_status in (0,4,5) and curdate() between bo.actcheckin_day and bo.actcheckout_day and bo.brush_status <> 1-- and bo.actcheckin_day <> bo.actcheckout_day   
            where lh.city_id <> 376
            and l.city_id <> 376
            and lh.state>=100
            group by lh.id
            ;"""


def house_static():
    sql = """INSERT INTO youjia_report.wh_house_static(house_id,lodge_id,house_state,lodge_state,lodge_state_super,lodge_new,city_id,city_name,city_pinyin,work_id,work_name,district_id,district_name,service_id,service_name,online_sale_id,online_sale,online_email,offline_sale_id,offline_sale,offline_email,shopowner_id,shopowner,shopowner_email,city_owner_id,city_owner,city_owner_email,dis_owner_id,dis_owner,dis_owner_email,landlord_id,landlord_name,join_type,invest_id,invest_name, title,lodge_title,house_title,house_title_sub,area,kitchen,latlng,room_num,parlor_num,balcony,toilet_num,address,community,house_product_type,house_type,is_elevator,month_rent,create_time,modify_time,own_type) 

        select 
        concat(lh.id,"") house_id,
        concat(lh.lodge_id,"") lodge_id,
        concat(lh.state,"") house_state,
        concat(l.state,"") lodge_state,
        concat(l.state_super,"") lodge_state_super,
        if (timestampdiff(day,lhot.create_time, now())>30,0,1) lodge_new ,
        concat(c.id,"") city_id,
        c.short_name city_name,
        c.pinyin city_pinyin ,
        concat(uo.id,"")  work_id,
        uo.name work_name, 
        concat(uo3.id,"")  district_id, 
        uo3.name district_name,
        concat(lh.service_id,"") service_id,
        ifnull(usc.sname,'服务中心为空') service_name,
        group_concat(distinct users1.id) online_sale_id,
        group_concat(distinct users1.realname) online_sale,
        group_concat(distinct users1.email separator ";") online_email,
        
        group_concat(distinct users2.id) offline_sale_id,
        group_concat(distinct users2.realname) offline_sale,
        group_concat(distinct users2.email separator ";") offline_email,
        
        group_concat(distinct users3.id) shopowner_id,
        group_concat(distinct users3.realname) shopowner,
        group_concat(distinct users3.email separator ";") shopowner_email,
        
        group_concat(distinct users4.id) city_owner_id,
        group_concat(distinct users4.realname) city_owner,
        group_concat(distinct users4.email separator ";") city_owner_email,
        
        group_concat(distinct users5.id) dis_owner_id,
        group_concat(distinct users5.realname) dis_owner,
        group_concat(distinct users5.email separator ";") dis_owner_email,
       
        
        concat(u1.id,"") landlord_id,
        u1.realname landlord_name,
        
        lh.join_type , -- '加盟类型，0暂时未定类型，默认都是0，1投资人投资的房子',
        if(lh.own_type=1 and lh.join_type<>1,null,concat(u1.id,"")) invest_id, -- 投资人id
        if(lh.own_type=1 and lh.join_type<>1,null,u1.realname) invest_name, -- 投资人姓名
        
        
        lh.title_sub title,
        l.title lodge_title,
        lh.title house_title, 
        lh.title_sub house_title_sub,
        
        concat(lh.area,"") area,
        concat(lh.kitchen,"") kitchen,-- 厨房
        concat(lh.latlng,"") latlng,
        concat(lh.room_num,"") room_num,-- 卧室数
        concat(lh.parlor_num,"") parlor_num, -- 客厅数
        concat(lh.balcony,"") balcony, -- 阳台
        concat(lh.toilet_num,"") toilet_num, -- 卫生间数
        concat(lh.address,"") address, -- 详细地址
        concat(lh.community,"") community, -- 小区名称
        concat(lh.house_product_type,"") house_product_type, -- 房屋产品类型(10:标准房,20:严选房)
        concat(lh.house_type,"") house_type, -- 房源类型：1民居、2公寓、3别墅、4Loft复式、5四合院、6老洋房、7客栈、8房车
        concat(lh.is_elevator,"") is_elevator,  -- 是否有电梯，true有，false没有
        concat(if (DATE_SUB(curdate(),INTERVAL 1 DAY) between date(ich.lease_date_begin) and date(ich.lease_date_end),
        case
        when DATE_SUB(curdate(),INTERVAL 1 DAY) >= date(ich.lease_date_begin) and DATE_SUB(curdate(),INTERVAL 1 DAY) <  DATE_SUB(date(ich.lease_date_begin),INTERVAL -1 year) then SUBSTRING_INDEX(ich.rent_grow_ratio,',',1)
        when DATE_SUB(curdate(),INTERVAL 1 DAY) >= DATE_SUB(date(ich.lease_date_begin),INTERVAL -1 year) and DATE_SUB(curdate(),INTERVAL 1 DAY) < DATE_SUB(date(ich.lease_date_begin),INTERVAL -2  year) then SUBSTRING_INDEX(ich.rent_grow_ratio,',',2)
        when DATE_SUB(curdate(),INTERVAL 1 DAY) >= DATE_SUB(date(ich.lease_date_begin),INTERVAL -2 year) and DATE_SUB(curdate(),INTERVAL 1 DAY) < DATE_SUB(date(ich.lease_date_begin),INTERVAL -3  year) then SUBSTRING_INDEX(ich.rent_grow_ratio,',',3)
        when DATE_SUB(curdate(),INTERVAL 1 DAY) >= DATE_SUB(date(ich.lease_date_begin),INTERVAL -3 year) and DATE_SUB(curdate(),INTERVAL 1 DAY) < DATE_SUB(date(ich.lease_date_begin),INTERVAL -4  year) then SUBSTRING_INDEX(ich.rent_grow_ratio,',',4)
        when DATE_SUB(curdate(),INTERVAL 1 DAY) >= DATE_SUB(date(ich.lease_date_begin),INTERVAL -4 year) and DATE_SUB(curdate(),INTERVAL 1 DAY) < DATE_SUB(date(ich.lease_date_begin),INTERVAL -5  year) then SUBSTRING_INDEX(ich.rent_grow_ratio,',',5)
        else 0
        end  * 100+ich.property_fee/12+ich.heating_fee/12+ich.net_fee/12  ,0),"") month_rent ,
        now() create_time,
        now() modify_time,
        lh.own_type
        -- online_state,lodge_ids,
        
        from youjia.lod_house lh 
        
        left join youjia.lodge l on l.id = lh.lodge_id
        
        left join youjia.city c on c.id = lh.city_id
        left join youjia.user_org uo on uo.id=lh.work_id and uo.is_delete = 0
        left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
        left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
        LEFT JOIN youjia.user_org_service_center usc ON usc.sid=lh.service_id
        
        left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1  
        LEFT JOIN youjia.users users1 ON uhr1.user_id=users1.id
        left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
        LEFT JOIN youjia.users users2 ON uhr2.user_id=users2.id
        
        left join youjia.invest_compact_house ich on ich.house_id = lh.id
        left join youjia.invest_relation_record irr on lh.id = irr.lod_house_id and irr.date_relieve is null
        
        left join youjia.users u1 on u1.id = lh.landlord_id
        
        -- left join youjia.users u on u.id = lh.landlord_id
        
        left join youjia.user_org_role uor3 on uor3.org_id=uo.id and uor3.is_delete = 0 and uor3.role_id = 73 and uor3.user_id not in (SELECT distinct user_id FROM youjia.user_org_role where org_id in (SELECT id FROM youjia.user_org where type in (10,20)) and is_delete = 0)
        left join youjia.users users3 on users3.id=uor3.user_id
        
        left join youjia.user_org_role uor4 on uor4.org_id=uo2.id and uor4.is_delete = 0  and uor4.role_id = 3  and uor4.user_id not in (SELECT distinct user_id FROM youjia.user_org_role where org_id in (SELECT id FROM youjia.user_org where type in (10,20)) and is_delete = 0)
        left join youjia.users users4 on users4.id=uor4.user_id
        
        left join youjia.user_org_role uor5 on uor5.org_id=uo3.id and uor5.is_delete = 0  and uor5.role_id = 2  and uor5.user_id not in (SELECT distinct user_id FROM youjia.user_org_role where org_id in (SELECT id FROM youjia.user_org where type in (10)) and is_delete = 0)
        left join youjia.users users5 on users5.id=uor5.user_id
        
        left join youjia.lod_house_online_time lhot on lh.id=lhot.lod_house_id  
        left join youjia.lod_house_online_time lhot1 on lhot1.lod_house_id=lhot.lod_house_id and lhot.id > lhot1.id
        where lh.city_id <> 376
        and l.city_id <> 376
        and lh.state = 100
        and lhot1.id is null
        group by lh.id
        on duplicate key update house_id = values(house_id),lodge_id = values(lodge_id),house_state = values(house_state),lodge_state = values(lodge_state),lodge_state_super = values(lodge_state_super),lodge_new = values(lodge_new),city_id = values(city_id),city_name = values(city_name),city_pinyin = values(city_pinyin),work_id = values(work_id),work_name = values(work_name),district_id = values(district_id),district_name = values(district_name),service_id = values(service_id),service_name = values(service_name),online_sale_id = values(online_sale_id),online_sale = values(online_sale),online_email=values(online_email),offline_sale_id = values(offline_sale_id),offline_sale = values(offline_sale),offline_email=values(offline_email),shopowner_id = values(shopowner_id),shopowner = values(shopowner),shopowner_email=values(shopowner_email),city_owner_id = values(city_owner_id),city_owner = values(city_owner),city_owner_email = values(city_owner_email),dis_owner_id = values(dis_owner_id),dis_owner = values(dis_owner),dis_owner_email = values(dis_owner_email),landlord_id = values(landlord_id),landlord_name = values(landlord_name),join_type = values(join_type),invest_id = values(invest_id),invest_name = values(invest_name), title = values(title),lodge_title = values(lodge_title),house_title = values(house_title),house_title_sub = values(house_title_sub),area = values(area),kitchen = values(kitchen),latlng = values(latlng),room_num = values(room_num),parlor_num = values(parlor_num),balcony = values(balcony),toilet_num = values(toilet_num),address = values(address),community = values(community),house_product_type = values(house_product_type),house_type = values(house_type),is_elevator = values(is_elevator),month_rent = values(month_rent), create_time = values(create_time),modify_time = values(modify_time),own_type = values(own_type)
        
        ;"""
    return sql


def level_house(dt):
    """
    level_type 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
    :param dt:
    :return:
    """
    sql = """INSERT INTO youjia_report.wh_level_calendar(level_name,level_id,dt,level_type,online_night,offline_night,order_night,available_night,income,gmv,rent,airbnb_check_out_count,tujia_check_out_count,zhenguo_check_out_count,other_check_out_count,order_check_out_count,create_time,modify_time) 
            select  
            whs.title level_name,-- 级别名称
            whs.house_id level_id, -- 级别id
            '{dt}' dt, -- 数据所属日期
            0 level_type,-- 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
            sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) online_night, -- 在线间夜
            sum(if(whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 0 and have_order = 0,1,0)) offline_night , -- 关房间夜
            sum(have_order) order_night ,-- 已售间夜
            sum(if((whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 1 )or have_order = 1,1,0))  available_night ,-- 可售间夜
            round(sum(income)) income ,-- 成功订单收入
            round(sum(compensate_income + other_income+income)) gmv, -- GMV不含刷单
            round(sum(rent_day)) rent ,-- 租金,
            sum(if(checkout_channel = 20,checkout_orders,0)) airbnb_check_out_count,
            sum(if(checkout_channel = 3,checkout_orders,0)) tujia_check_out_count,
            sum(if(checkout_channel = 25,checkout_orders,0)) zhenguo_check_out_count,
            sum(if(checkout_channel not in (3,20,25),checkout_orders,0)) other_check_out_count,
            sum(checkout_orders) order_check_out_count, -- 离店订单数量
            now() create_time ,
            now() modify_time
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt = '{dt}' 
            and whs.own_type in (1,2)
            and whs.city_id <> 376
            and whc.order_type <> 3
            -- and whs.work_name not like '%特殊门店'
            group by whs.house_id
            on duplicate key update level_name = values(level_name),online_night = values(online_night),offline_night = values(offline_night),order_night = values(order_night),available_night = values(available_night),income = values(income),gmv = values(gmv),rent = values(rent),airbnb_check_out_count = values(airbnb_check_out_count),tujia_check_out_count = values(tujia_check_out_count),zhenguo_check_out_count = values(zhenguo_check_out_count),other_check_out_count = values(other_check_out_count),order_check_out_count = values(order_check_out_count),create_time = values(create_time),modify_time = values(modify_time)
            ;""".format(dt=dt)
    return sql


def level_online_sale(dt):
    """
    level_type 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
    :param dt:
    :return:
    """
    sql = """INSERT INTO youjia_report.wh_level_calendar(level_name,level_id,dt,level_type,online_night,offline_night,order_night,available_night,income,gmv,rent,airbnb_check_out_count,tujia_check_out_count,zhenguo_check_out_count,other_check_out_count,order_check_out_count,create_time,modify_time) 
            select  
            whs.online_sale level_name,-- 级别名称
            whs.online_sale_id level_id, -- 级别id
            '{dt}' dt, -- 数据所属日期
            1 level_type,-- 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
            sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) online_night, -- 在线间夜
            sum(if(whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 0 and have_order = 0,1,0)) offline_night , -- 关房间夜
            sum(have_order) order_night ,-- 已售间夜
            sum(if((whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 1 )or have_order = 1,1,0))  available_night ,-- 可售间夜
            round(sum(income)) income ,-- 成功订单收入
            round(sum(compensate_income + other_income+income)) gmv, -- GMV不含刷单
            round(sum(rent_day)) rent ,-- 租金,
            sum(if(checkout_channel = 20,checkout_orders,0)) airbnb_check_out_count,
            sum(if(checkout_channel = 3,checkout_orders,0)) tujia_check_out_count,
            sum(if(checkout_channel = 25,checkout_orders,0)) zhenguo_check_out_count,
            sum(if(checkout_channel not in (3,20,25),checkout_orders,0)) other_check_out_count,
            sum(checkout_orders) order_check_out_count, -- 离店订单数量
            now() create_time ,
            now() modify_time
            
            from youjia_report.wh_house_static whs
            left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
            where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
            and whc.dt = '{dt}' 
            and whs.own_type in (1,2)
            and whc.order_type <> 3
            and whs.city_id <> 376
            and online_sale_id is not null and online_sale_id not like "%,%"
            -- and whs.work_name not like '%特殊门店'
            group by whs.online_sale_id
            on duplicate key update level_name = values(level_name),online_night = values(online_night),offline_night = values(offline_night),order_night = values(order_night),available_night = values(available_night),income = values(income),gmv = values(gmv),rent = values(rent),airbnb_check_out_count = values(airbnb_check_out_count),tujia_check_out_count = values(tujia_check_out_count),zhenguo_check_out_count = values(zhenguo_check_out_count),other_check_out_count = values(other_check_out_count),order_check_out_count = values(order_check_out_count),create_time = values(create_time),modify_time = values(modify_time)
            ;""".format(dt=dt)
    return sql


def level_work(dt):
    """
    level_type 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
    :param dt:
    :return:
    """
    sql = """INSERT INTO youjia_report.wh_level_calendar(level_name,level_id,dt,level_type,online_night,offline_night,order_night,available_night,income,gmv,rent,airbnb_check_out_count,tujia_check_out_count,zhenguo_check_out_count,other_check_out_count,order_check_out_count,create_time,modify_time) 
                select  
                whs.work_name level_name,-- 级别名称
                whs.work_id level_id, -- 级别id
                '{dt}' dt, -- 数据所属日期
                2 level_type,-- 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
                sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) online_night, -- 在线间夜
                sum(if(whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 0 and have_order = 0,1,0)) offline_night , -- 关房间夜
                sum(have_order) order_night ,-- 已售间夜
                sum(if((whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 1 )or have_order = 1,1,0))  available_night ,-- 可售间夜
                round(sum(income)) income ,-- 成功订单收入
                round(sum(compensate_income + other_income+income)) gmv, -- GMV不含刷单
                round(sum(rent_day)) rent ,-- 租金,
                sum(if(checkout_channel = 20,checkout_orders,0)) airbnb_check_out_count,
                sum(if(checkout_channel = 3,checkout_orders,0)) tujia_check_out_count,
                sum(if(checkout_channel = 25,checkout_orders,0)) zhenguo_check_out_count,
                sum(if(checkout_channel not in (3,20,25),checkout_orders,0)) other_check_out_count,
                sum(checkout_orders) order_check_out_count, -- 离店订单数量
                now() create_time ,
                now() modify_time

                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt = '{dt}' 
                and whs.own_type in (1,2)
                and whc.order_type <> 3
                and whs.city_id <> 376
                and work_id is not null
                -- and whs.work_name not like '%特殊门店'
                group by whs.work_id
                on duplicate key update level_name = values(level_name),online_night = values(online_night),offline_night = values(offline_night),order_night = values(order_night),available_night = values(available_night),income = values(income),gmv = values(gmv),rent = values(rent),airbnb_check_out_count = values(airbnb_check_out_count),tujia_check_out_count = values(tujia_check_out_count),zhenguo_check_out_count = values(zhenguo_check_out_count),other_check_out_count = values(other_check_out_count),order_check_out_count = values(order_check_out_count),create_time = values(create_time),modify_time = values(modify_time)
                ;""".format(dt=dt)
    return sql


def level_city(dt):
    """
    level_type 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
    :param dt:
    :return:
    """
    sql = """INSERT INTO youjia_report.wh_level_calendar(level_name,level_id,dt,level_type,online_night,offline_night,order_night,available_night,income,gmv,rent,airbnb_check_out_count,tujia_check_out_count,zhenguo_check_out_count,other_check_out_count,order_check_out_count,create_time,modify_time) 
                select  
                whs.city_name level_name,-- 级别名称
                whs.city_id level_id, -- 级别id
                '{dt}' dt, -- 数据所属日期
                3 level_type,-- 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
                sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) online_night, -- 在线间夜
                sum(if(whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 0 and have_order = 0,1,0)) offline_night , -- 关房间夜
                sum(have_order) order_night ,-- 已售间夜
                sum(if((whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 1 )or have_order = 1,1,0))  available_night ,-- 可售间夜
                round(sum(income)) income ,-- 成功订单收入
                round(sum(compensate_income + other_income+income)) gmv, -- GMV不含刷单
                round(sum(rent_day)) rent ,-- 租金,
                sum(if(checkout_channel = 20,checkout_orders,0)) airbnb_check_out_count,
                sum(if(checkout_channel = 3,checkout_orders,0)) tujia_check_out_count,
                sum(if(checkout_channel = 25,checkout_orders,0)) zhenguo_check_out_count,
                sum(if(checkout_channel not in (3,20,25),checkout_orders,0)) other_check_out_count,
                sum(checkout_orders) order_check_out_count, -- 离店订单数量
                now() create_time ,
                now() modify_time

                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt = '{dt}' 
                and whs.own_type in (1,2)
                and whc.order_type <> 3
                and whs.city_id <> 376
                and city_id is not null
                and whs.work_name not like '%特殊门店'
                group by whs.city_id
                on duplicate key update level_name = values(level_name),online_night = values(online_night),offline_night = values(offline_night),order_night = values(order_night),available_night = values(available_night),income = values(income),gmv = values(gmv),rent = values(rent),airbnb_check_out_count = values(airbnb_check_out_count),tujia_check_out_count = values(tujia_check_out_count),zhenguo_check_out_count = values(zhenguo_check_out_count),other_check_out_count = values(other_check_out_count),order_check_out_count = values(order_check_out_count),create_time = values(create_time),modify_time = values(modify_time)
                ;""".format(dt=dt)
    return sql


def level_district(dt):
    """
    level_type 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
    :param dt:
    :return:
    """
    sql = """INSERT INTO youjia_report.wh_level_calendar(level_name,level_id,dt,level_type,online_night,offline_night,order_night,available_night,income,gmv,rent,airbnb_check_out_count,tujia_check_out_count,zhenguo_check_out_count,other_check_out_count,order_check_out_count,create_time,modify_time) 
                select  
                whs.district_name level_name,-- 级别名称
                whs.district_id level_id, -- 级别id
                '{dt}' dt, -- 数据所属日期
                4 level_type,-- 等级划分：0:代表房屋，1:代表线上销售,2:代表服务门店,3:代表城市 4:代表区域',
                sum(if(whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1),1,0)) online_night, -- 在线间夜
                sum(if(whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 0 and have_order = 0,1,0)) offline_night , -- 关房间夜
                sum(have_order) order_night ,-- 已售间夜
                sum(if((whs.house_state >= 100 and whc.online_stock = 1 and rent_stock = 1 )or have_order = 1,1,0))  available_night ,-- 可售间夜
                round(sum(income)) income ,-- 成功订单收入
                round(sum(compensate_income + other_income+income)) gmv, -- GMV不含刷单
                round(sum(rent_day)) rent ,-- 租金,
                sum(if(checkout_channel = 20,checkout_orders,0)) airbnb_check_out_count,
                sum(if(checkout_channel = 3,checkout_orders,0)) tujia_check_out_count,
                sum(if(checkout_channel = 25,checkout_orders,0)) zhenguo_check_out_count,
                sum(if(checkout_channel not in (3,20,25),checkout_orders,0)) other_check_out_count,
                sum(checkout_orders) order_check_out_count, -- 离店订单数量
                now() create_time ,
                now() modify_time

                from youjia_report.wh_house_static whs
                left join youjia_report.wh_house_calendar whc on whc.house_id = whs.house_id
                where whs.house_state >= 100 and (whc.online_stock = 1 or have_order = 1)
                and whc.dt = '{dt}' 
                and whs.own_type in (1,2)
                and whc.order_type <> 3
                and whs.city_id <> 376
                and district_id is not null
                and whs.work_name not like '%特殊门店'
                group by whs.district_id
                on duplicate key update level_name = values(level_name),online_night = values(online_night),offline_night = values(offline_night),order_night = values(order_night),available_night = values(available_night),income = values(income),gmv = values(gmv),rent = values(rent),airbnb_check_out_count = values(airbnb_check_out_count),tujia_check_out_count = values(tujia_check_out_count),zhenguo_check_out_count = values(zhenguo_check_out_count),other_check_out_count = values(other_check_out_count),order_check_out_count = values(order_check_out_count),create_time = values(create_time),modify_time = values(modify_time)
                ;""".format(dt=dt)
    return sql


def insert(dt=None):
    with db_util.get_connection() as conn:
        try:
            conn.query(house_static())
        except:
            log.error(level_district(dt))

        try:
            conn.query(level_house(dt))
        except:
            log.error(level_house(dt))

        try:
            conn.query(level_online_sale(dt))
        except:
            log.error(level_online_sale(dt))

        try:
            conn.query(level_work(dt))
        except:
            log.error(level_work(dt))

        try:
            conn.query(level_city(dt))
        except:
            log.error(level_city(dt))

        try:
            conn.query(level_district(dt))
        except:
            log.error(level_district(dt))


# with db_util.get_connection() as conn:
#     conn.query(level_district('2019-12-15'))

# st = date_util.str_to_date('2017-06-01')
# st = date_util.str_to_date('2019-11-01')

def process(process_curdate=False):
    if process_curdate:
        curdate = date_util.curdate(0)
        insert(curdate)
        log.info("----------->处理当天数据【{}】完成<-----------".format(curdate))
        return

    yesterday = date_util.curdate(1)
    st = date_util.date_sub(yesterday, months=2)
    et = date_util.date_add(yesterday, months=6)

    while st <= et:
        insert(st)
        log.info("处理循环天数数据数据日期为:【{}】".format(st))
        st = date_util.date_add(st, days=1)


if __name__ == '__main__':

    args = sys.argv
    if args.__len__() < 2:
        process()
    else:
        process(args[1])
